﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DusonFramework.QueryHelper.SQL
{
    /// <summary>
    /// 查询辅助类
    /// </summary>
    /// 2008-9-16 chenpm
    public class SearchHelper
    {
        #region 私有变量

        private List<SearchInfo> _list = new List<SearchInfo>(); // 条件列表

        #endregion

        #region 公有方法

        #region 增加条件

        /// <summary>
        /// 增加条件(不加入条件的默认值为空)
        /// </summary>
        /// <param name="fielName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="operatorType">操作符类型</param>
        public void Add(string fielName, object fieldValue, OperatorType operatorType)
        {
            this._list.Add(new SearchInfo(fielName, fieldValue, operatorType));
        }

        /// <summary>
        /// 增加条件
        /// </summary>
        /// <param name="fielName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="operatorType">操作符类型</param>
        /// <param name="excludeValue">不加入条件的值</param>
        public void Add(string fielName, object fieldValue, OperatorType operatorType, object excludeValue)
        {
            this._list.Add(new SearchInfo(fielName, fieldValue, operatorType, excludeValue));
        }

        #endregion

        /// <summary>
        /// 构造参数形式的SQL语句和参数数组的键值对
        /// </summary>
        /// <returns></returns>
        public KeyValuePair<string, SqlParameter[]> BuildParameter()
        {
            StringBuilder sbSQL = new StringBuilder("1=1");
            List<SqlParameter> paraList = new List<SqlParameter>();

            string sqlFormat = " AND {0} {1} @{0}{2}";
            object sqlValue;
            SearchInfo info;
            for (int i = 0; i < _list.Count; i++)
            {
                info = _list[i];

                // 不加入条件
                if (info.FieldValue.Equals(info.ExcludeValue))
                    continue;

                // SQL语句
                sbSQL.AppendFormat(sqlFormat, info.FieldName, this.ConvertOperator(info.OperatorType), i);

                // 参数数组
                if (info.OperatorType == OperatorType.Like)
                    sqlValue = string.Format("%{0}%", info.FieldValue);
                else
                    sqlValue = info.FieldValue;
                paraList.Add(new SqlParameter("@" + info.FieldName + i.ToString(), sqlValue));
            }

            return new KeyValuePair<string, SqlParameter[]>(sbSQL.ToString(), paraList.ToArray());
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 转换枚举类型为对应的Sql语句操作符号
        /// </summary>
        /// <param name="operatorType">OperatorType枚举对象</param>
        /// <returns>对应的Sql语句操作符号（如 ">" "<>"</returns>
        private string ConvertOperator(OperatorType operatorType)
        {
            string stringOperator = " = ";
            switch (operatorType)
            {
                case OperatorType.Equal:
                    stringOperator = " = ";
                    break;
                case OperatorType.LessThan:
                    stringOperator = " < ";
                    break;
                case OperatorType.LessThanOrEqual:
                    stringOperator = " <= ";
                    break;
                case OperatorType.Like:
                    stringOperator = " Like ";
                    break;
                case OperatorType.MoreThan:
                    stringOperator = " > ";
                    break;
                case OperatorType.MoreThanOrEqual:
                    stringOperator = " >= ";
                    break;
                case OperatorType.NotEqual:
                    stringOperator = " <> ";
                    break;
                default:
                    break;
            }

            return stringOperator;
        }

        #endregion

    }
}
